package studio.raptor.ddal.dashboard.ddl;

import oracle.sql.TIMESTAMP;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import studio.raptor.ddal.dashboard.util.DateUtil;
import studio.raptor.ddal.jdbc.RaptorDataSource;

import javax.sql.DataSource;
import java.math.BigDecimal;
import java.sql.*;

/**
 * 智慧运营BSS技术架构测试用例
 * @author liujy
 */
public class DdlExecuteUtil
{
    private  static Logger logger = LoggerFactory.getLogger( DdlExecuteUtil.class);
    private  DataSource dataSource=null;
    private  Connection connection;

    public void init(DataSource dataSource,Connection connection){
        this.dataSource=dataSource;
        this.connection=connection;
    }

    public void init(String virtualDbName,String databaseType) throws Exception{
        try {
            this.dataSource = new RaptorDataSource( virtualDbName, databaseType );
            this.connection = this.dataSource.getConnection();
        }catch(Exception e){
            throw e;
        }
    }

    /**
     * DDL 语句执行
     * @param sql
     * @return
     * @throws Exception
     */
    public String executeDdl(String sql) throws Exception{
        long beginTime=System.currentTimeMillis();
        try(PreparedStatement preparedStatement=connection.prepareStatement(sql);)
        {
            preparedStatement.execute();
            long endTime=System.currentTimeMillis();
            long time=endTime-beginTime;
            return "操作成功，耗时 "+time+" ms";
        }
    }

    /**
     * DML语句-增、删、改执行
     * @param sql
     * @return
     * @throws Exception
     */
    public String executeIUD(String sql) throws Exception{
        long beginTime=System.currentTimeMillis();
        try(
                PreparedStatement preparedStatement=connection.prepareStatement(sql);
        ){
            int result=preparedStatement.executeUpdate();
            connection.commit();
            long endTime=System.currentTimeMillis();
            long time=endTime-beginTime;
            return "操作成功，影响行数： "+result+"，耗时 "+time+" ms";
        }
    }

    /**
     * DML语句-查询执行
     * @param sql
     * @return
     * @throws Exception
     */
    public String executeSelect(String sql) throws Exception{
        long beginTime=System.currentTimeMillis();
        try( PreparedStatement preparedStatement=connection.prepareStatement(sql);){
            ResultSet resultSet=preparedStatement.executeQuery();
            ResultSetMetaData rsmd = resultSet.getMetaData();
            String respMsg="";
            int num=0;
            if(rsmd != null){
                int count=rsmd.getColumnCount();
                String[] colName=new String[count];
                for(int i=0;i<count;i++){
                    colName[i]=rsmd.getColumnName(i+1);
                }
                while (resultSet.next())
                {
                    String tempRespMsg="";
                    String tempCol="";
                    int index=0;
                    for( ; index<count; index++){
                        Object colValue=resultSet.getObject(colName[index] );
//                    System.out.println(colValue.getClass());
                        if(null!=colValue && colValue instanceof java.sql.Date)
                        {
                            String valueStr=DateUtil.getFormatTimeString((Date)colValue,"yyyy-MM-dd HH:mm:ss" );
                            tempCol="\""+colName[index]+"\":\""+ valueStr +"\"";
                        }
                        else if(null!=colValue && colValue instanceof java.sql.Time){
                            String valueStr=DateUtil.getFormatTimeString((Time)colValue,"yyyy-MM-dd HH:mm:ss" );
                            tempCol="\""+colName[index]+"\":\""+ valueStr +"\"";
                        }
                        else if(null!=colValue && colValue instanceof java.sql.Timestamp ){
                            String valueStr=DateUtil.getFormatTimeString((Timestamp)colValue,"yyyy-MM-dd HH:mm:ss" );
                            tempCol="\""+colName[index]+"\":\""+ valueStr +"\"";
                        }
                        else if(null!=colValue &&  colValue instanceof oracle.sql.TIMESTAMP ){
                            String valueStr=DateUtil.getFormatTimeString((TIMESTAMP)colValue,"yyyy-MM-dd HH:mm:ss" );
                            tempCol="\""+colName[index]+"\":\""+ valueStr +"\"";
                        }
                        else if(null!=colValue && colValue instanceof java.math.BigDecimal){
                            String valueStr=((BigDecimal)colValue).longValue()+"";
                            tempCol="\""+colName[index]+"\":\""+ valueStr +"\"";
                        }
                        else if(null!=colValue && colValue instanceof java.sql.Clob){
                            String valueStr=((Clob)colValue).toString();
                            tempCol="\""+colName[index]+"\":\""+ valueStr +"\"";
                            System.out.println( "DdlExecuteUtil.executeSelect:" +resultSet.getString( colName[index] ));
                        }
                        else{
                            tempCol="\""+colName[index]+"\":\""+resultSet.getString( colName[index] )+"\"";
                        }
                        if(index==0){
                            tempRespMsg += tempCol;
                        }else{
                            tempRespMsg += ","+tempCol;
                        }
                    }
                    if("".equals( respMsg )){
                        respMsg = "{"+ tempRespMsg+"}";
                    }
                    else{
                        respMsg=respMsg +",{"+ tempRespMsg+"}";
                    }
                    num++;
                }
            }
            else{
                //sql = "select "+name+".nextval" 序列处理
                if(sql.toLowerCase().indexOf( "nextval")!=-1 && sql.toLowerCase().indexOf( "from")==-1){
                    if(resultSet.next()){
                        long seq = resultSet.getLong( 1 );
                        respMsg="{\"nextval\":"+seq+"}";
                        num++;
                    }
                }
            }
            long endTime=System.currentTimeMillis();
            long time=endTime-beginTime;
            if("".equals( respMsg )){
                respMsg="{\"respMsg\":\"查询结果集为空，耗时 "+time+"ms\"}";
            }
            else{
                respMsg="{\"respMsg\":\"查询结果集总记录数为 "+ num + "，耗时 "+time+"ms\",\"respData\":["+ respMsg +"]}";
            }
            return respMsg;
        }
    }

}
